﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.OleDb;
using System.Collections;
using System.Collections.Generic;

/// <summary>
///数据库操作类，所有方法为静态
/// </summary>
public class OleDbHelper
{
    private static OleDbCommand cmd;
    private static OleDbDataAdapter sda;
    private static DataSet ds;
    private static DataTable dt;
    private static OleDbDataReader sdr;
    private static OleDbTransaction trans;
    public OleDbHelper()
	{
		//
		//TODO: 在此处添加构造函数逻辑
		//
	}

    //数据库连接
    private static string getcon()
    {
        string conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["dbpath"].ToString());
        return conn;
    }

    // 哈希表用来存储缓存的参数信息，哈希表可以存储任意类型的参数。
    private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());

    #region  执行简单SQL语句

    #region 执行一条简单的SQL语句
    /// <summary>
    /// 执行一条Sql语句，返回一个String形
    /// </summary>
    /// <param name="sql">String sql</param>
    /// /// <param name="db">String db</param>
    /// <returns>String</returns>
    public static string SExecuteScalarSql(string sql)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            using (cmd = new OleDbCommand(sql, connn))
            {
                try
                {
                    connn.Open();
                    string rows = Convert.ToString(cmd.ExecuteScalar());
                    return rows;
                }
                catch (Exception e)
                {
                    connn.Close();
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connn.Close();
                }
            }
        }
    }

    #endregion

    #region 执行一条sql语句，返回影响的记录数
    /// <summary>
    /// 执行一条sql语句，返回影响的记录数
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteNonSql(string sql)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            using (cmd = new OleDbCommand(sql, connn))
            {
                try
                {
                    connn.Open();
                    int row = cmd.ExecuteNonQuery();
                    return row;
                }
                catch (Exception e)
                {
                    connn.Close();
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connn.Close();
                }
            }
        }
    }
    #endregion

    #region 执行一条Sql语句，返回结果集的第一行第一列（Float形）

    /// <summary>
    /// 执行一条Sql语句，返回结果集的第一行第一列（Float形）
    /// </summary>
    /// <param name="sql"></param>
    /// <returns>float </returns>
    public static float FExecuteScalarSqlNoP(string sql)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            using (cmd = new OleDbCommand(sql, connn))
            {
                try
                {
                    connn.Open();
                    float rows = Convert.ToSingle(cmd.ExecuteScalar());
                    return rows;
                }
                catch (Exception e)
                {
                    connn.Close();
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connn.Close();
                }
            }
        }
    }
    #endregion

    #region 执行一条Sql语句，返回结果集的第一行第一列
    /// <summary>
    /// 执行一条Sql语句，返回结果集的第一行第一列
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteScalarSql(string sql)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            using (cmd = new OleDbCommand(sql, connn))
            {
                try
                {
                    connn.Open();
                    int rows = Convert.ToInt32(cmd.ExecuteScalar());
                    return rows;
                }
                catch (Exception e)
                {
                    connn.Close();
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connn.Close();
                }
            }
        }
    }
    #endregion

    #region 执行一条SQL语句,返回一个DataTable
    /// <summary>
    /// 执行一条SQL语句,返回一个DataTable
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns>返回DataTable</returns>
    public static DataTable GetDataTable(string sql)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            using (sda = new OleDbDataAdapter())
            {
                try
                {
                    connn.Open();
                    sda.SelectCommand = new OleDbCommand(sql, connn);
                    dt = new DataTable("saftable");
                    sda.Fill(dt);
                    return dt;
                }
                catch (Exception e)
                {
                    connn.Close();
                    throw e;
                }
                finally
                {
                    sda.SelectCommand.Dispose();
                    connn.Close();
                }
            }
        }
    }
    #endregion

    #region 执行一条SQL语句，返回一个DataSet
    /// <summary>
    /// 执行一条SQL语句，返回一个DataSet
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>返回一个DataSet</returns>
    public static DataSet GetDataSet(String SQLString)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            try
            {
                connn.Open();
                sda = new OleDbDataAdapter(SQLString, connn);
                ds = new DataSet();
                sda.Fill(ds);
                ds.Tables.Add();
                return ds;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                connn.Close();
                throw e;
            }
            finally
            {
                connn.Close();
            }
        }
    }

    #endregion

    #region  执行一条SQL语句，返回一个OleDbDataAdapter
    /// <summary>
    /// 执行一条SQL语句，返回一个OleDbDataAdapter 
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>返回一个OleDbDataAdapter</returns>
    public static OleDbDataAdapter GetDataAdapter(string SQLString)
    {
        OleDbConnection connn = new OleDbConnection(getcon());
        try
        {
            connn.Open();
            sda = new OleDbDataAdapter(SQLString, connn);
            return sda;
        }
        catch (System.Data.SqlClient.SqlException e)
        {
            connn.Close();
            throw e;
        }
        finally
        {
            connn.Close();
        }

    }
    #endregion

    #region 执行一条SQL语句，返回一个OleDbDataReader( 注意：调用该方法后，一定要对OleDbDataReader进行Close )

    /// <summary>
    /// 执行一条SQL语句，返回一个OleDbDataReader
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>返回一个OleDbDataReader</returns>
    public static OleDbDataReader GetDataReader(string SQLString)
    {
        using (cmd = new OleDbCommand())
        {
            OleDbConnection connn = new OleDbConnection(getcon());
            try
            {
                
                cmd.Connection = connn;
                cmd.Connection.Open();
                cmd.CommandText = SQLString;
                sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return sdr;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                cmd.Connection.Close();
                throw e;
            }
        }
    }

    #endregion

    #region 执行多条SQL语句，实现数据库事务
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>		
    public static int ExecuteSqlTran(List<String> SQLStringList)
    {
        using (cmd = new OleDbCommand())
        {
            OleDbConnection connn = new OleDbConnection(getcon());
            cmd.Connection = connn;
            connn.Open();
            OleDbTransaction tx = connn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                int count = 0;
                for (int n = 0; n < SQLStringList.Count; n++)
                {
                    string strsql = SQLStringList[n];
                    if (strsql.Trim().Length > 1)
                    {
                        cmd.CommandText = strsql;
                        count += cmd.ExecuteNonQuery();
                    }
                }
                tx.Commit();
                return count;
            }
            catch
            {
                tx.Rollback();
                connn.Close();
                return 0;
            }
            finally
            {
                cmd.Dispose();
                connn.Close();
            }
        }
    }
    #endregion

    #endregion

    #region 执行带参数的SQL语句

    #region 执行一条带参数SQL语句，返回影响的记录数

    /// <summary>
    /// 执行一条带参数SQL语句，返回结果集的第一行第一列
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>返回结果集的第一行第一列</returns>
    public static int ExecuteSql(string SQLString,params OleDbParameter[] cmdParms)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            using (cmd = new OleDbCommand())
            {
                try
                {
                    PrepareCommand(cmd, connn, null, SQLString, cmdParms);
                    if (cmd.ExecuteScalar() == null)
                    {
                        cmd.Parameters.Clear();
                        connn.Close();
                        return 0;
                    }
                    else
                    {
                        int rows = Convert.ToInt32(cmd.ExecuteScalar());
                        cmd.Parameters.Clear();
                        connn.Close();
                        return rows;
                    }

                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connn.Close();
                }
            }
        }
    }

    #endregion

    #region 执行一条带参数的sql语句（如：update,delete,insert），返回影响的记录行数
    ///<summary>
    ///执行一条带参数的sql语句（如：update,delete），返回影响的记录行数
    ///</summary>
    ///<param name="sql">带参数的sql语句</param>
    ///<returns>影响的记录数</returns>
    public static int ExecuteNonSql(string sql, params OleDbParameter[] cmdParams)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            using (cmd = new OleDbCommand())
            {
                try
                {
                    PrepareCommand(cmd, connn, null, sql, cmdParams);
                    int row = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return row;
                }
                catch (Exception e)
                {
                    connn.Close();
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connn.Close();
                }
            }
        }
    }
    #endregion

    #region 执行一条带参数SQL语句，返回影响的记录数
    /// <summary>
    /// 执行一条带参数SQL语句，返回影响的记录数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="cmdParms"></param>
    /// <returns></returns>
    protected static int ExecuteScalarSql(string SQLString, params OleDbParameter[] cmdParms)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            using (cmd = new OleDbCommand())
            {
                try
                {
                    PrepareCommand(cmd, connn, null, SQLString, cmdParms);
                    int rows = Convert.ToInt32(cmd.ExecuteScalar());
                    cmd.Parameters.Clear();
                    connn.Close();
                    return rows;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw e;
                }
                finally
                {
                    cmd.Dispose();
                    connn.Close();
                }
            }
        }
    }
    #endregion

    #region 执行多条带参数SQL语句，实现数据库事务。
    /// <summary>
    /// 执行多条带参数SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OleDbParameter[]）</param>
    public static void ExecuteSqlTran(int flag, Hashtable SQLStringList)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            connn.Open();
            using (trans = connn.BeginTransaction())
            {
                cmd = new OleDbCommand();
                try
                {
                    //循环
                    foreach (DictionaryEntry myDE in SQLStringList)
                    {
                        string cmdText = myDE.Key.ToString();
                        OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value;
                        PrepareCommand(cmd, connn, trans, cmdText, cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    trans.Commit();
                }
                catch
                {
                    trans.Rollback();
                    throw;
                }
                finally
                {
                    connn.Close();
                }
            }
        }
    }

    #endregion

    #region 执行带参数查询语句，返回OleDbDataReader ( 注意：调用该方法后，一定要对OleDbDataReader进行Close )
    /// <summary>
    /// 执行带参数查询语句，返回OleDbDataReader ( 注意：调用该方法后，一定要对OleDbDataReader进行Close )
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>OleDbDataReader</returns>
    public static OleDbDataReader GetDataReader(string SQLString, params OleDbParameter[] cmdParms)
    {
        cmd = new OleDbCommand();
        OleDbConnection connn = new OleDbConnection(getcon());
        try
        {
            PrepareCommand(cmd, connn, null, SQLString, cmdParms);
            sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return sdr;
        }
        catch (System.Data.SqlClient.SqlException e)
        {
            connn.Close();
            throw e;
        }
    }
    #endregion

    #region 执行带参数查询语句，返回DataTable
    /// <summary>
    /// 执行带参数查询语句，返回DataTable
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataTable</returns>
    public static DataTable GetDataTable(string SQLString, params OleDbParameter[] cmdParms)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            cmd = new OleDbCommand();
            PrepareCommand(cmd, connn, null, SQLString, cmdParms);
            using (sda = new OleDbDataAdapter(cmd))
            {
                dt = new DataTable("table2");
                try
                {
                    sda.Fill(dt);
                    cmd.Parameters.Clear();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    connn.Close();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connn.Close();
                }
                return dt;
            }
        }
    }
    #endregion

    #region 执行带参数查询语句，返回DataSet
    /// <summary>
    /// 执行带参数查询语句，返回DataSet
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet GetDataSet(string SQLString, params OleDbParameter[] cmdParms)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            cmd = new OleDbCommand();
            PrepareCommand(cmd, connn, null, SQLString, cmdParms);
            using (sda = new OleDbDataAdapter(cmd))
            {
                ds = new DataSet();
                try
                {
                    sda.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    connn.Close();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connn.Close();
                }
                return ds;
            }
        }
    }
    #endregion

    #region 执行带参数查询语句，返回OleDbDataAdapter
    /// <summary>
    /// 执行带参数查询语句，返回OleDbDataAdapter
    /// </summary>
    /// <param name="SQLString">查询语句</param>
    /// <returns>OleDbDataAdapter</returns>
    public static OleDbDataAdapter GetDataAdapter(string SQLString, params OleDbParameter[] cmdParms)
    {
        OleDbConnection connn = new OleDbConnection(getcon());
        OleDbCommand cmd = new OleDbCommand(SQLString, connn);
        cmd.Parameters.AddRange(cmdParms);
        try
        {

            connn.Open();
            sda = new OleDbDataAdapter(cmd);
            return sda;
        }
        catch (System.Data.SqlClient.SqlException e)
        {
            connn.Close();
            throw e;
        }
        finally
        {
            connn.Close();
        }

    }
    #endregion

    #region 执行带参数的SQL语句的公用方法
    private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
    {
        if (conn.State != ConnectionState.Open)
            conn.Open();
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        if (trans != null)
            cmd.Transaction = trans;
        cmd.CommandType = CommandType.Text;//cmdType;
        if (cmdParms != null)
        {


            foreach (OleDbParameter parameter in cmdParms)
            {
                if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                    (parameter.Value == null))
                {
                    parameter.Value = DBNull.Value;
                }
                cmd.Parameters.Add(parameter);
            }
        }
    }
    #endregion

    #endregion

    #region  存储过程操作

    /// <summary>
    /// 执行不带参数存储过程，返回OleDbDataReader ( 注意：调用该方法后，一定要对OleDbDataReader进行Close )
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <returns>OleDbDataReader</returns>
    public static OleDbDataReader ProceGetDataReaderNoP(string procName)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            try
            {
                connn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = connn;
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteReader();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                connn.Close();
                throw e;
            }
            finally
            {
                connn.Close();
            }
        }
    }

    /// <summary>
    /// 执行一个无参增删改存储过程，返回影响行数
    /// </summary>
    /// <param name="procName">存储过程名字</param>
    /// <returns>影响行数</returns>
    public static int ProcExcuteNoP(string procName)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            try
            {
                connn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = connn;
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                return cmd.ExecuteNonQuery();
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                connn.Close();
                throw e;
            }
            finally
            {
                connn.Close();
            }
        }
    }


    /// <summary>
    /// 执行不带参数存储过程,返回DataTable
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <returns>DataTable</returns>
    public static DataTable ProcGetDataTableNoP(string procName)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            try
            {
                DataTable dt = new DataTable("proceDTNoP");
                connn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = connn;
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                OleDbDataAdapter sda = new OleDbDataAdapter();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                return dt;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                connn.Close();
                throw e;
            }
            finally
            {
                connn.Close();
            }
        }
    }



    /// <summary>
    /// 执行带参数存储过程，返回OleDbDataReader ( 注意：调用该方法后，一定要对OleDbDataReader进行Close )
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns>OleDbDataReader</returns>
    public static OleDbDataReader ProcGetDataReaderP(string storedProcName, IDataParameter[] parameters)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            try
            {
                connn.Open();
                OleDbCommand cmd = BuildQueryCommand(connn, storedProcName, parameters);
                cmd.CommandType = CommandType.StoredProcedure;
                OleDbDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return sdr;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                connn.Close();
                throw e;
            }
            finally
            {
                connn.Close();
            }
        }
    }


    /// <summary>
    /// 执行带参数存储过程,返回DataTable
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns>DataTable</returns>
    public static DataTable ProcGetDataTableP(string storedProcName, IDataParameter[] parameters)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            try
            {
                DataTable dt = new DataTable("proceDT");
                connn.Open();
                OleDbDataAdapter sda = new OleDbDataAdapter();
                sda.SelectCommand = BuildQueryCommand(connn, storedProcName, parameters);
                sda.Fill(dt);
                connn.Close();
                return dt;
            }
            catch (System.Data.SqlClient.SqlException e)
            {
                connn.Close();
                throw e;
            }
            finally
            {
                connn.Close();
            }
        }
    }

    /// <summary>
    /// 执行带参数存储过程，返回影响的行数		
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <param name="rowsAffected">影响的行数</param>
    /// <returns></returns>
    public static int ProcExcuteP(string storedProcName, IDataParameter[] parameters)
    {
        using (OleDbConnection connn = new OleDbConnection(getcon()))
        {
            try
            {
                int result;
                connn.Open();
                OleDbCommand cmd = BuildIntCommand(connn, storedProcName, parameters);
                result = cmd.ExecuteNonQuery();
                int aa = (int)cmd.Parameters["ReturnValue"].Value;
                return result;
            }
            catch (Exception e)
            {
                connn.Close();
                throw e;
            }
            finally
            {
                connn.Close();
            }
        }
    }
    /// <summary>
    /// 构建 OleDbCommand 对象(用来返回一个结果集，而不是一个整数值)
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns>OleDbCommand</returns>
    public static OleDbCommand BuildQueryCommand(OleDbConnection connection, string storedProcName, IDataParameter[] parameters)
    {
        using (OleDbCommand command = new OleDbCommand(storedProcName, connection))
        {
            try
            {
                command.CommandType = CommandType.StoredProcedure;
                foreach (OleDbParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        // 检查未分配值的输出参数,将其分配以DBNull.Value.
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                            (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }

                return command;
            }
            catch (Exception e)
            {
                connection.Close();
                throw e;
            }
        }
    }

    /// <summary>
    /// 创建 OleDbCommand 对象实例(用来返回一个整数值)	
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns>OleDbCommand 对象实例</returns>
    private static OleDbCommand BuildIntCommand(OleDbConnection connection, string storedProcName, IDataParameter[] parameters)
    {
        try
        {
            OleDbCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new OleDbParameter("ReturnValue",
                OleDbType.Integer, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }
        catch (Exception e)
        {
            connection.Close();
            throw e;
        }
    }

    #endregion

}
